import os
import re

import xlrd
import pandas as pd
from pony import orm
from tqdm import tqdm
from postgres.db_build import MedicineNormalize, ServiceNormalize, ConsumableNormalize, Hospital_coding, \
    Hospital_mapping

@orm.db_session()
def yibao2medicine(input_path):
    xl_path = "/home/kv/workspace/查药扣费/主成分剂型2药品.xlsx"
    df = pd.read_excel(xl_path, dtype=str, keep_default_na=False)

    data = pd.ExcelFile(input_path)
    sheet_names = data.sheet_names
    table = []

    for sheet_name in sheet_names:
        df_in = pd.read_excel(input_path, dtype=str, keep_default_na=False, sheet_name=sheet_name)
        df_in.insert(0, "标准名称", "")
        for i in tqdm(range(0, len(df_in))):
            series = pd.Series(dict(
                医保编码="",
                标准名称="",
                主成分="",
                剂型="",
                医保类型="",
                自负比例="",
                备注=""
            ))
            item = df_in.iloc[i, :]
            zcf = item["药品名称"]
            jx = item["剂型"]
            if zcf and jx:
                search_res = df[(df["zcf"]==zcf) & (df["jx"]==jx)]["name"].to_list()
                if search_res:
                    standard_name = search_res[0]
                    if standard_name:
                        series["标准名称"] = standard_name
                        df_in.loc[i, "标准名称"] = standard_name
                        qs = MedicineNormalize.select(standard_name=standard_name)
                        if len(qs)>0:
                            for it in qs:
                                series["医保编码"]=it.nhsa_code
                series["主成分"] = zcf
                series["剂型"] = jx
                series["医保类型"] = item["医保类型"]
                if "自负比例" in item:
                    series["自负比例"] = item["自负比例"]
                series["备注"] = item["备注"]
                table.append(series)
            elif zcf:
                standard_name = item["药品名称"]
                if re.search("[()（）]", standard_name):
                    standard_name = re.sub("[(（].*?[)）]", "", standard_name)
                if standard_name:
                    series["标准名称"] = standard_name
                    df_in.loc[i, "标准名称"] = standard_name
                    qs = MedicineNormalize.select(standard_name=standard_name)
                    if len(qs) > 0:
                        for it in qs:
                            series["医保编码"] = it.nhsa_code
                    series["医保类型"] = item["医保类型"]
                    if "自负比例" in item:
                        series["自负比例"] = item["自负比例"]
                    series["备注"] = item["备注"]
                    table.append(series)
        break
    df_out = pd.DataFrame(table)
    df_out = df_out.fillna("")
    df_out.to_excel(input_path.replace(os.path.basename(input_path), "药品目录.xlsx"), index=False)

    # df_in = df_in.fillna("")
    # df_in.to_excel(input_path.replace(os.path.basename(input_path), "药品目录.xlsx"), index=False)



if __name__ == '__main__':
    yibao2medicine("/kv-data/sw/查药扣费/数据/38-烟台市/城镇职工、居民医疗保险药品目录信息_0.xlsx")

